package com.wontube.smr.dao.providers;

import static org.apache.ibatis.jdbc.SqlBuilder.BEGIN;
import static org.apache.ibatis.jdbc.SqlBuilder.FROM;
import static org.apache.ibatis.jdbc.SqlBuilder.INNER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.LEFT_OUTER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.ORDER_BY;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT_DISTINCT;
import static org.apache.ibatis.jdbc.SqlBuilder.SQL;

import java.util.Map;

import com.wontube.core.dao.mybatis.QueryExample;
import com.wontube.core.dao.mybatis.provider.SqlProvider;


/**
 * @author yuechao
 *
 */
public class TVShowSqlProvider extends SqlProvider {
	//Japanese Chinese Korea
	public String selectWithDramaByGenre(Map<String, Object> map){
		QueryExample example = (QueryExample) map.get("example");
		BEGIN();
		if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_show.id");
        } else {
            SELECT("tv_show.id");
        }
		SELECT("tv_show.area, tv_show.name, tv_show.description, tv_show.director, tv_show.actors, tv_show.banner_img_url");
	    SELECT("tv_show.thumbnail, tv_show.score, tv_show.season_count, tv_show.episode_cnt, tv_show.genre");
	    SELECT("tv_show.air_begin_date, tv_show.air_end_date, tv_show.website, tv_show.video_id, tv_show.version");
	    SELECT("tv_show.item_url, tv_show.sort_index ,tv_show.category");
	    FROM("tv_show");
	    INNER_JOIN("tvshow_genre on tv_show.id=tvshow_genre.show_id");
	    //where tv_show.website=" + SiteConstants.WEBSITE_CRUNCHYROLL + " and tv_show.category=" + 
	    //TVShow.CATEGORY_DRAMA + " and tvshow_genre.genre_name=" + tvShowGenreName
	    
	    String tvShowGenreName = (String)map.get("genreName");
	    applyInCondition("tvshow_genre.genre_name", tvShowGenreName);

	    applyWhere(example, true);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
	    
	    return SQL();
	}
	
	
	public String selectWithRecent(QueryExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_show.id");
        } else {
            SELECT("tv_show.id");
        }
        
        SELECT("tv_show.area, tv_show.name, tv_show.description, tv_show.director, tv_show.actors, tv_show.banner_img_url");
        SELECT("tv_show.thumbnail, tv_show.score, tv_show.season_count, tv_show.episode_cnt, tv_show.genre");
        SELECT("tv_show.air_begin_date, tv_show.air_end_date, tv_show.website, tv_show.video_id, tv_show.version");
        SELECT("tv_show.item_url, tv_show.sort_index");

        FROM("tv_show");
        INNER_JOIN("recent_show on tv_show.id = recent_show.show_id");
        
        applyWhere(example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        String sql = SQL();
        return sql;
	}
	
	public String countWithRecent(QueryExample example) {
        BEGIN();

        SELECT("count(*)");
        
        FROM("tv_show");
        INNER_JOIN("recent_show on tv_show.id = recent_show.show_id");
        
        applyWhere(example, false);
        
        String sql = SQL();
        return sql;
	}
	
	public String selectWithToday(QueryExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_show.id");
        } else {
            SELECT("tv_show.id");
        }
        
        SELECT("tv_show.area, tv_show.name, tv_show.description, tv_show.director, tv_show.actors, tv_show.banner_img_url");
        SELECT("tv_show.thumbnail, tv_show.score, tv_show.season_count, tv_show.episode_cnt, tv_show.genre");
        SELECT("tv_show.air_begin_date, tv_show.air_end_date, tv_show.website, tv_show.video_id, tv_show.version");
        SELECT("tv_show.item_url, tv_show.sort_index");

        FROM("tv_show");
        INNER_JOIN("tvshow_today on tv_show.id = tvshow_today.show_id");
        
        applyWhere(example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        String sql = SQL();
        return sql;
	}
	
	public String countWithToday(QueryExample example) {
        BEGIN();

        SELECT("count(*)");
        
        FROM("tv_show");
        INNER_JOIN("tvshow_today on tv_show.id = tvshow_today.show_id");
        
        applyWhere(example, false);
        
        String sql = SQL();
        return sql;
	}


	public String selectWithRecentByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_show.id");
        } else {
            SELECT("tv_show.id");
        }
        
        SELECT("tv_show.area, tv_show.name, tv_show.description, tv_show.director, tv_show.actors, tv_show.banner_img_url");
        SELECT("tv_show.thumbnail, tv_show.score, tv_show.season_count, tv_show.episode_cnt, tv_show.genre");
        SELECT("tv_show.air_begin_date, tv_show.air_end_date, tv_show.website, tv_show.video_id, tv_show.version");
        SELECT("tv_show.item_url, tv_show.sort_index");

        FROM("tv_show");
        LEFT_OUTER_JOIN("recent_show on tv_show.id = recent_show.show_id");
        INNER_JOIN("tvshow_genre on tv_show.id = tvshow_genre.show_id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
        
        applyWhere(example, true);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        String sql = SQL();
        return sql;
	}
	
	public String countWithRecentByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();

        SELECT("count(*)");
        
        FROM("tv_show");
        LEFT_OUTER_JOIN("recent_show on tv_show.id = recent_show.show_id");
        INNER_JOIN("tvshow_genre on tv_show.id = tvshow_genre.show_id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
		
        applyWhere(example, true);
        
        String sql = SQL();
        return sql;
	}
	
	public String countWithByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();

        SELECT("count(*)");
        FROM("tv_show");
        INNER_JOIN("tvshow_genre on tv_show.id = tvshow_genre.show_id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
        
        applyWhere(example, true);
        
        return SQL();
	}
	
	public String selectWithByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();

        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_show.id");
        } else {
            SELECT("tv_show.id");
        }
        
        SELECT("tv_show.area, tv_show.name, tv_show.description, tv_show.director, tv_show.actors, tv_show.banner_img_url");
        SELECT("tv_show.thumbnail, tv_show.score, tv_show.season_count, tv_show.episode_cnt, tv_show.genre");
        SELECT("tv_show.air_begin_date, tv_show.air_end_date, tv_show.website, tv_show.video_id, tv_show.version");
        SELECT("tv_show.item_url, tv_show.sort_index");

        FROM("tv_show");
        INNER_JOIN("tvshow_genre on tv_show.id = tvshow_genre.show_id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
        
        applyWhere(example, true);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        return SQL();
	}
}
